package com.library.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

public class MySQLBookTitleDAO implements BookTitleDAO {

	@Override
	public boolean insert(BookTitle bookTitle) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "INSERT INTO `booktitles` (`title`, `author`, `description`, `publisher`) VALUES ('"
					+ bookTitle.getTitle()
					+ "','"
					+ bookTitle.getAuthor()
					+ "','"
					+ bookTitle.getDescription()
					+ "','"
					+ bookTitle.getPublisher() + ")";
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public boolean remove(int bookTitleId) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "DELETE FROM `booktitles` WHERE `booktitleid` = " + bookTitleId ;
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public boolean update(BookTitle bookTitle) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "UPDATE `booktitles` SET"
					+ "`title` ='" + bookTitle.getTitle() + "',"
					+ "`author` ='" + bookTitle.getAuthor() + "',"
					+ "`description` ='" + bookTitle.getDescription() + "',"
					+ "`publisher` ='" + bookTitle.getPublisher() + "'"
					+ "WHERE"
					+ "`booktitleid` =" + bookTitle.getBookTitleId() ;
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public BookTitle findByKey(int bookTitleId) {
		Connection conn = MySQLConnection.getConnection();
		BookTitle bookTitle = null;
		if (conn!= null){
			String querry = "SELECT * FROM `booktitles` WHERE booktitleid = " + bookTitleId;
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				if (result.next()){
					bookTitle = new BookTitle();
					bookTitle.setBookTitleId(bookTitleId);
					bookTitle.setAuthor(result.getString("author"));
					bookTitle.setDescription(result.getString("description"));
					bookTitle.setPublisher(result.getString("publisher"));
					bookTitle.setTitle(result.getString("title"));

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return bookTitle;
	}

	@Override
	public ArrayList<BookTitle> findAll() {
		ArrayList<BookTitle> bookTitles = null;
		Connection conn = MySQLConnection.getConnection();
		if (conn!= null){
			String querry = "SELECT * FROM `booktitles` WHERE 1";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				bookTitles = new ArrayList<BookTitle>();
				while (result.next()){
					BookTitle bookTitle = new BookTitle();
					bookTitle.setBookTitleId(result.getInt("booktitleid"));
					bookTitle.setAuthor(result.getString("author"));
					bookTitle.setDescription(result.getString("description"));
					bookTitle.setPublisher(result.getString("publisher"));
					bookTitle.setTitle(result.getString("title"));
					bookTitles.add(bookTitle);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return bookTitles;
	}

	@Override
	public ArrayList<BookTitle> search(String title, String author, String publisher) {
		ArrayList<BookTitle> bookTitles = null;
		Connection conn = MySQLConnection.getConnection();
		if (conn!= null){
			String querry = "SELECT * FROM `booktitles` WHERE "
					+ "`title` LIKE '%" + title
					+ "%' AND `author` LIKE '%" + author
					+ "%' AND `publisher` LIKE '%" + publisher + "%'";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				bookTitles = new ArrayList<BookTitle>();
				while (result.next()){
					BookTitle bookTitle = new BookTitle();
					bookTitle.setBookTitleId(result.getInt("booktitleid"));
					bookTitle.setAuthor(result.getString("author"));
					bookTitle.setDescription(result.getString("description"));
					bookTitle.setPublisher(result.getString("publisher"));
					bookTitle.setTitle(result.getString("title"));
					bookTitles.add(bookTitle);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return bookTitles;
	}

	@Override
	public ArrayList<BookTitle> getNRandomBook(int n) {
		ArrayList<BookTitle> bookTitles = null;
		Connection conn = MySQLConnection.getConnection();
		if (conn!= null){
			String querry = "SELECT * FROM `booktitles` ORDER BY RAND()LIMIT " + n;
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				bookTitles = new ArrayList<BookTitle>();
				while (result.next()){
					BookTitle bookTitle = new BookTitle();
					bookTitle.setBookTitleId(result.getInt("booktitleid"));
					bookTitle.setAuthor(result.getString("author"));
					bookTitle.setDescription(result.getString("description"));
					bookTitle.setPublisher(result.getString("publisher"));
					bookTitle.setTitle(result.getString("title"));
					bookTitles.add(bookTitle);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}

		}
		return bookTitles;
	}

	@Override
	public int getNoBookWithState(String state, int bookTitleId) {
		String query = "SELECT COUNT(bookid) FROM bookitems WHERE ((booktitleid = "+bookTitleId+") AND (status = '"+state+"'))";
		Connection conn = MySQLConnection.getConnection();
		int count = -1;
		if (conn != null){
			try {
				Statement st = conn.createStatement();
				ResultSet res = st.executeQuery(query);
				while (res.next()){
					count = res.getInt(1);
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
		}
		return count;
	}

}
